package com.contactmanager;

import java.util.ArrayList;
import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ContactsDataSource {
	
	/*
	 * Private members
	 */
	private SQLiteDatabase db;
	private ContactsSQLiteHelper dbHelper;
	private String[] allColumns = { 
			ContactsSQLiteHelper.COLUMN_ID,
			ContactsSQLiteHelper.COLUMN_NAME,
			ContactsSQLiteHelper.COLUMN_ADDRESS,
			ContactsSQLiteHelper.COLUMN_CELL,
			ContactsSQLiteHelper.COLUMN_ALT,
			ContactsSQLiteHelper.COLUMN_EMAIL,
			ContactsSQLiteHelper.COLUMN_COMMENTS,
			ContactsSQLiteHelper.COLUMN_GROUP
			};
	
	private String[] allGroups = {
			ContactsSQLiteHelper.COLUMN_GROUP_ID,
			ContactsSQLiteHelper.COLUMN_GROUP_NAME
	};
	
	
	/*
	 * Constructor
	 */
	public ContactsDataSource(Context context){
		dbHelper = new ContactsSQLiteHelper(context);
	}
	
	/*
	 * DB operations
	 */
	public void open() throws SQLException{
		db = dbHelper.getWritableDatabase();
	}
	
	public void close(){
		dbHelper.close();
	}
	
	/*
	 * Common queries
	 */
	
	//Returns all contacts
	private Cursor queryAll(){
		Cursor cursor = db.query(ContactsSQLiteHelper.TABLE_CONTACTS, allColumns, null, null, null, null, null);
		return cursor;
	}
	
	//Returns all groups ???
	
	private Cursor queryAllGroups(){
		Cursor cursor2 = db.query(ContactsSQLiteHelper.TABLE_GROUPS, allGroups, null, null, null, null, null, null);
		return cursor2;
	}
	

	/*
	 * Add, update, delete operations
	 */
	public Contact addContact(Contact c){
		addContact(c.getName(), c.getAddress(), c.getCell(), c.getAlt(), c.getEmail(), c.getComments(), c.getGroup());
		
		return c;
	}
	
	public Contact addContact(
			String name,
			String address,
			String cell,
			String alt,
			String email,
			String comments,
			int groupsid
			){
		
		//Prepare INSERT query, add column names and values
		ContentValues values = new ContentValues();
		values.put(ContactsSQLiteHelper.COLUMN_NAME,name);
		values.put(ContactsSQLiteHelper.COLUMN_ADDRESS,address);
		values.put(ContactsSQLiteHelper.COLUMN_CELL,cell);
		values.put(ContactsSQLiteHelper.COLUMN_ALT,alt);
		values.put(ContactsSQLiteHelper.COLUMN_EMAIL,email);
		values.put(ContactsSQLiteHelper.COLUMN_COMMENTS,comments);
		values.put(ContactsSQLiteHelper.COLUMN_GROUP,groupsid);
		
		//INSERT into contacts table
		long insertId = db.insert(ContactsSQLiteHelper.TABLE_CONTACTS, null, values);
		
		//Get a cursor pointing to the newly created contact
		Cursor cursor = db.query(ContactsSQLiteHelper.TABLE_CONTACTS, allColumns, ContactsSQLiteHelper.COLUMN_ID + " = " + insertId, null, null, null, null);
		cursor.moveToFirst();
		
		Contact newContact = cursorToContact(cursor);
		
		cursor.close();
		
		return newContact;
	}
	
	//Update contact 
	public Contact updateContact(Contact contact) throws Exception{
		String NAME_UPDATE = "UPDATE contacts SET name= " + "'" + contact.getName() + "'" + " WHERE _id = " + contact.getId();
		String ADDRESS_UPDATE = "UPDATE contacts SET address= " + "'" + contact.getAddress() + "'" + " WHERE _id = " + contact.getId();
		String CELL_UPDATE = "UPDATE contacts SET cell= " + "'" + contact.getCell() + "'" + " WHERE _id = " + contact.getId();
		String ALT_UPDATE = "UPDATE contacts SET alt= " + "'" + contact.getAlt() + "'" + " WHERE _id = " + contact.getId();
		String EMAIL_UPDATE = "UPDATE contacts SET email= " + "'" + contact.getEmail() + "'" + " WHERE _id = " + contact.getId();
		String COMMENTS_UPDATE = "UPDATE contacts SET comments= " + "'" + contact.getComments() + "'" + " WHERE _id = " + contact.getId();
		String GROUP_UPDATE = "UPDATE contacts SET groupsid= " + "'" + contact.getGroup() + "'" + " WHERE _id = " + contact.getId();
		
		db.execSQL(NAME_UPDATE);
		db.execSQL(ADDRESS_UPDATE);
		db.execSQL(CELL_UPDATE);
		db.execSQL(ALT_UPDATE);
		db.execSQL(EMAIL_UPDATE);
		db.execSQL(COMMENTS_UPDATE);
		db.execSQL(GROUP_UPDATE);

		return contact;
	}
	
	public void deleteContact(Contact contact) throws Exception{
		int id = (int)contact.getId();
		db.execSQL("DELETE FROM contacts WHERE _id = '" + id + "'");
	}
	
	//Update Group Name
	public Group updateGroups(Group group) throws Exception{
		String GROUP_UPDATE = "UPDATE groups SET groupname= " + "'" + group.getGroupName() + "'" + " WHERE groupid = " + group.getGroupId();
		db.execSQL(GROUP_UPDATE);
		return group;
	}
	
	/*public void testContact(){
		String DATABASE_TESTDATA = "INSERT INTO contacts (name, address, cell, alt, email, comments, cat1, cat2, cat3, cat4, cat5) VALUES (" + 
				"\"Chris Williams\",\"123 E. Marquette St. Appleton, WI\",\"9202686853\",\"9209547244\", " + 
				"\"willic82@uwosh.edu\",\"SE Major\", 0, 0, 0, 0, 0)"; 
		db.execSQL(DATABASE_TESTDATA);
	}*/
	
	/*
	 * Get methods
	 */
	public ArrayList<Contact> getAllContacts(){	
		Cursor cursor = queryAll();
		ArrayList<Contact> contacts = cursorToContactList(cursor);
		for(Contact c : contacts){
			Log.d("CM", "ID: " + c.getId() + " NAME: " + c.getName());
		}
		cursor.close();
		return contacts;
	}
	
	public Contact getContactById(long id){
		Cursor c = db.query(ContactsSQLiteHelper.TABLE_CONTACTS, allColumns, ContactsSQLiteHelper.COLUMN_ID + " = " + id, null, null, null, null);
		if(c.getCount() == 0){
			Log.d("CM", "Couldn't find contact with id " + id);
			return null;
		}
		c.moveToFirst();
		return cursorToContact(c);
	}
	
	public ArrayList<Contact> getContactsGroup(int groupID){
		String table1=ContactsSQLiteHelper.TABLE_CONTACTS;
		//String table2=ContactsSQLiteHelper.TABLE_GROUPS;
		//String groupIDColumn= ContactsSQLiteHelper.COLUMN_GROUP_ID;
		String contactGroupID = ContactsSQLiteHelper.COLUMN_GROUP;
		//String MY_QUERY = "SELECT * FROM "+table1+ "AS a, "+table2+" AS b INNER JOIN a."+contactGroupID+" ON b."+groupIDColumn+" WHERE a."+contactGroupID+" AND b."+groupIDColumn+"=?";
		ArrayList<Contact> contacts;
		Cursor cursor;
		if(groupID!=1){
			String MY_QUERY = "SELECT * FROM "+table1+ " WHERE "+contactGroupID+" = ?";
			cursor = db.rawQuery(MY_QUERY,new String[]{""+groupID});
			contacts = cursorToContactList(cursor);
		}
		else{
			cursor = db.query(ContactsSQLiteHelper.TABLE_CONTACTS, allColumns, null, null, null, null, null);
			contacts = cursorToContactList(cursor);
		}
		//Cursor cursor = db.query(ContactsSQLiteHelper.TABLE_CONTACTS, allColumns, null, null, null, null, null);
		
		for(Contact c : contacts){
			Log.d("CM", "ID: " + c.getId() + " NAME: " + c.getName());
		}
		cursor.close();
		return contacts;
	}
	
	public ArrayList<Group> getAllGroups(){	
		Cursor cursor = queryAllGroups();
		ArrayList<Group> groups = cursorToGroupList(cursor);
		for(Group g : groups){
			Log.d("CM", "ID: " + g.getGroupId() + " NAME: " + g.getGroupName());
		}
		cursor.close();
		return groups;
	}	
	
	/*
	 * Conversion methods
	 */
	private Contact cursorToContact(Cursor cursor){
		Contact contact = new Contact();
		contact.setId(cursor.getLong(0));
		contact.setName(cursor.getString(1));
		contact.setAddress(cursor.getString(2));
		contact.setCell(cursor.getString(3));
		contact.setAlt(cursor.getString(4));
		contact.setEmail(cursor.getString(5));
		contact.setComments(cursor.getString(6));
		contact.setGroup(cursor.getInt(7));
		return contact;
	}
	
	private Group cursorToGroup(Cursor cursor){
		Group group = new Group();
		group.setGroupId(cursor.getLong(0));
		group.setGroupName(cursor.getString(1));
		
		return group;
	}
	
	private ArrayList<Contact> cursorToContactList(Cursor cursor){
		ArrayList<Contact> list = new ArrayList<Contact>();
		cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      Contact contact = cursorToContact(cursor);
	      list.add(contact);
	      cursor.moveToNext();
	    }
	    return list;
	}
	
	private ArrayList<Group> cursorToGroupList(Cursor cursor){
		ArrayList<Group> list = new ArrayList<Group>();
		cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      Group group = cursorToGroup(cursor);
	      list.add(group);
	      cursor.moveToNext();
	    }
	    return list;
	}
	
	public void removeAll()
	{
		open();
	    db.delete(ContactsSQLiteHelper.TABLE_CONTACTS, null, null);
	    close();
	}
}
